﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE
Public Class ListaPoaHistoricoDA

    Public Function ListaPoaHistorico(ByVal poah As String, ByVal Version As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select h.poa, h.nombre,h.objetivo,h.meta,un.descripcion as unidad,monto_total_invertido,meta2,un2.descripcion as unidad2,monto_inv_anhio,meta3,un3.descripcion as unidad3, " & _
                      "anhio_firma_poa,mes_firma_poa,ub.nombre as departamento,ti.descripcion as tipo ," & _
                      "snip,h.fecha_viabilidad,h.convenio,A.DESCRIPCION AS ambito,g.descripcion as estado,pp.abreviatura as PPER,h.documento,ej.descripcion as EJECUTOR,g1.descripcion as tp,h.version,pr.descripcion as PRODUCTO, " & _
                      "PM.NOMBRES || ' ' || PM.APELLIDO_PATERNO || ' ' || PM.APELLIDO_MATERNO AS MONITOREADOR , H.COORDX, H.COORDY, H.OBSERVACION," & _
                      "PE.NOMBRES || ' ' || PE.APELLIDO_PATERNO || ' ' || PE.APELLIDO_MATERNO AS ESPECIALISTA,H.MONTO_TRANSFERIDO, H.TIPO_PROYECTO_DES, TAR.DESCRIPCION AS TIPO_ASIGNACION_RECURSO " & _
                      "from poa_historico h " & _
                      "left join unidad un on un.unidad=h.unidad " & _
                      "LEFT JOIN unidad un2 ON un2.unidad = h.unidad2 " & _
                      "LEFT JOIN unidad un3 ON un3.unidad = h.unidad3 " & _
                      "left join ubigeo ub on ub.ubigeo=h.ubigeo " & _
                      "inner join tipo_proyecto_actividad ti on ti.tipo_proyecto_actividad=h.tipo_proyecto_actividad " & _
                      "left join pper pp on pp.pper=h.pper " & _
                      "left join ejecutor ej on ej.ejecutor=h.ejecutor " & _
                      "left join producto pr on pr.producto=h.producto " & _
                      "left join general g on g.codigo=h.estado and id='ESTADO_POA' " & _
                      "left join general g1 on g1.codigo=h.tipo_cambio_poa and g1.id='TIPO_CAMBIO' " & _
                      "LEFT JOIN PERSONA PM ON PM.PERSONA = h.PERSONA_MONITOREO " & _
                      "LEFT JOIN PERSONA PE ON PE.PERSONA = h.PERSONA_ESPECIALISTA " & _
                      "LEFT JOIN (SELECT CODIGO, DESCRIPCION FROM GENERAL WHERE ID = 'AMBITO' AND ESTADO = 'A') A " & _
                      "ON A.CODIGO = H.AMBITO " & _
                      "LEFT JOIN (SELECT CODIGO, DESCRIPCION FROM GENERAL WHERE ID = 'TIPO_ASIGNACION_RECURSO' AND ESTADO = 'A') TAR " & _
                      "ON TAR.CODIGO = H.TIPO_ASIGNACION_RECURSO " & _
                      "where H.poa='" + poah + "'  AND H.VERSION= '" + Version + "'"

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaHFinanciero(ByVal poahfin As String, ByVal hfinVersion As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select  p.poa, GE.GENERICA_GASTOS as generica ,EG.ESPECIFICA_GASTO as codigo ,EG.DESCRIPCION as especifica  ,p.META_01,p.META_02,p.META_03,p.META_04,p.META_05,p.META_06,p.META_07,p.META_08,p.META_09,p.META_10,p.META_11,p.META_12, " & _
                      "NVL(p.META_01,0) + NVL(p.META_02,0) + NVL(p.META_03,0) + NVL(p.META_04,0) + NVL(p.META_05,0) + NVL(p.META_06,0)+  NVL(p.META_07,0) + NVL(p.META_08,0) + NVL(p.META_09,0) + NVL(p.META_10,0) + " & _
                      " NVL(p.META_11,0) + NVL(p.META_12,0)  AS META_ANUAL " & _
                      "from POA_FINANCIERA_HISTORICO p " & _
                      "left join generica_gastos ge on GE.GENERICA_GASTOS=P.GENERICA_GASTO " & _
                      "left join especifica_gasto eg on EG.ESPECIFICA_GASTO=p.ESPECIFICA_GASTO  " & _
                      "where p.poa='" + poahfin + "' AND p.VERSION='" + hfinVersion + "'"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaHFisico(ByVal poahfis As String, ByVal hfisVersion As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT DISTINCT PHF.VERSION,COMP.DESCRIPCION AS COMPONENTE, U.DESCRIPCION AS UNIDAD,PHF.TAREA_ACTIVIDAD_DESCRIPCION AS TAREA ,PHF.META_01,PHF.META_02, " & _
                      "PHF.META_03, PHF.META_04,PHF.META_05,PHF.META_06,PHF.META_07,PHF.META_08,PHF.META_09,PHF.META_10, " & _
                      " PHF.META_11,PHF.META_12,( NVL(META_01, 0) +  NVL(META_02, 0) + NVL(META_03, 0) + NVL(META_04, 0) + NVL(META_05, 0) +NVL(META_06, 0) + " & _
                      " NVL(META_07, 0)  + NVL(META_08, 0) + NVL(META_09, 0) + NVL(META_10, 0) +NVL(META_11, 0) +  NVL(META_12,0) ) AS META_ANUAL, PHF.PESO ,PHF.CODIGO " & _
                      " FROM POA_FISICA_HISTORICO  PHF " & _
                      "INNER JOIN  POA_COMPONENTE_HISTORICO COMP ON  PHF.COMPONENTE = COMP.COMPONENTE AND PHF.POA = COMP.POA " & _
                      " LEFT JOIN UNIDAD U ON PHF.UNIDAD = U.UNIDAD " & _
                      " WHERE PHF.POA ='" + poahfis + "' AND PHF.VERSION='" + hfisVersion + "'  ORDER BY PHF.CODIGO"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function ListaHComponente(ByVal poahcom As String, ByVal hcomVersion As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "select PHC.POA,PHC.version,PHC.componente,PHC.descripcion as nombre, PHC.PESO_COMPONENTE " & _
                      "from POA_COMPONENTE_HISTORICO phc " & _
                      " where PHC.POA='" + poahcom + "' AND PHC.VERSION='" + hcomVersion + "' ORDER BY TO_NUMBER(phc.COMPONENTE)"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class
